use LabourManagement

insert into Salary values ('Grade 1',1000,'Luong Bac 1')
insert into Salary values ('Grade 2',2000,'Luong Bac 2')
insert into Salary values ('Grade 3',3000,'Luong Bac 3')
insert into Salary values ('Grade 4',4000,'Luong Bac 4')
insert into Salary values ('Grade 5',5000,'Luong Bac 5')
GO
insert into Labor values('La01','Nguyen Van A','abc1','0912345001','Single','1m70','70kg',1,'aaaa','Tran Van A','1 year','Grade 1','Tower 1',1)
insert into Labor values('La02','Nguyen Van B','abc2','0912345002','Single','1m71','71kg',1,'bbbb','Tran Van B','2 years','Grade 2','Tower 1',0)
insert into Labor values('La03','Nguyen Van C','abc3','0912345003','Married','1m72','72kg',0,'cccc','Tran Van C','2 years','Grade 3','Tower 2',1)
insert into Labor values('La04','Nguyen Van D','abc4','0912345004','Engaged','1m73','73kg',0,'dddd','Tran Van D','3 years','Grade 4','Tower 1',0)
insert into Labor values('La05','Nguyen Van E','abc5','0912345005','Single','1m74','74kg',0,'eeee','Tran Van E','3 year','Grade 5','Tower 3',1)
insert into Labor values('La06','Nguyen Van F','abc6','0912345006','Married','1m75','75kg',1,'ffff','Tran Van F','4 year','Grade 4','Tower 3',0)
insert into Labor values('La07','Nguyen Van G','abc7','0912345007','Single','1m76','76kg',0,'gggg','Tran Van G','1 year','Grade 3','Tower 2',0)
insert into Labor values('La08','Nguyen Van H','abc8','0912345008','Married','1m77','77kg',1,'hhhh','Tran Van H','1 year','Grade 5','Tower 1',1)
insert into Labor values('La09','Nguyen Van I','abc9','0912345009','Single','1m78','78kg',0,'iiii','None','2 year','Grade 1','Tower 2',0)
insert into Labor values('La10','Nguyen Van J','abc10','0945645645','Single','1m78','78kg',0,'jjjjjjjjjj','None','100 year','Grade 2','Tower 12',0)
GO
insert into Shift(ShiftID,ShiftName,TimeIn,TimeOut,Duration)
values ('Shift 3','Part Time Afternoon','22:00 PM','06:00 PM',8)

insert into Shift(ShiftID,ShiftName,TimeIn,TimeOut,Duration)
values ('Shift 2','Part Time Afternoon','14:00 PM','22:00 PM',8)
insert into Shift(ShiftID,ShiftName,TimeIn,TimeOut,Duration)
values ('Shift 1','Part Time Afternoon','06:00 PM','14:00 AM',8)
GO
insert into Attendance values ('La01','8/5/2013',1,'Shift 1',5,200000)
insert into Attendance values ('La02','8/5/2013',0,'Shift 2',4,40000)
insert into Attendance values ('La03','8/5/2013',1,'Shift 3',3,80000)
insert into Attendance values ('La04','8/5/2013',1,'Shift 1',2,256000)
insert into Attendance values ('La05','8/5/2013',0,'Shift 2',9,26000)
insert into Attendance values ('La06','8/5/2013',1,'Shift 3',5,205000)
insert into Attendance values ('La07','8/5/2013',0,'Shift 1',0,27000)
insert into Attendance values ('La08','8/5/2013',1,'Shift 2',0,20800)
insert into Attendance values ('La09','8/5/2013',1,'Shift 3',4,203000)
insert into Attendance values ('La10','8/5/2013',0,'Shift 1',0,20900)
GO


--------------------------------------------------------------------
/*
select * from Salary
select * from Labor
select * from Attendance
select * from Shift
select * from Manager
select [TimeOut],LaborID from Attendance Where [TimeOut] = '' and LaborID = 'La01'
go

---------------------------
                   
select * from Attendance where LaborID = 'La13'
go

------------------------------
select [LaborID], SUM (SalaryDaily)as Total from Attendance where [Day] between '2013-07-23' and '2013-07-29' group by LaborID
go

----------------------------


SELECT SalaryPerHour
FROM Labor INNER JOIN Salary ON Labor.SalaryGrade = Salary.SalaryGrade
where LaborID = 'La01'
go

-------------------------------
select datediff (hour,[TimeIn],[TimeOut]) as TimeWork from Attendance where LaborID = 'La01' and [Day] = '2013-07-23'
go

---------------------------------

select ExtraShift from Attendance where LaborID = 'La01' and [Day] = '2013-07-23'
go

--------------------------------
Tinh SalaryDaily ben JAVA
SalaryDaily = (SalaryPerHour * TimeWork) + 1.5 *(ExtraShift * SalaryPerHour)

--------------------------------

update Attendance set SalaryDaily  = /*Ket qua tinh duoc ben JaVa*/  where LaborID = 'La01' and [Day] = '2013-07-23'
go
update Attendance set [DailyAttendance]= 0,[ShiftID]='',[ExtraShift]= 1,[SalaryDaily] = 10000
where LaborID = 'La003' and [Day] = '2013-09-01'
select * from Attendance where LaborID = 'La003' and [Day] = '2014-09-01'
---------------------------------
SELECT     dbo.Shift.Duration
FROM         dbo.Attendance INNER JOIN
                      dbo.Shift ON dbo.Attendance.ShiftID = dbo.Shift.ShiftID
WHERE ShiftID = 'Sh01'

select SUM ([ExtraShift])as [Total Extra Shift] from Attendance where [Day] 
                 between '2013-08-01' and '2013-08-31' and LaborID = 'La005'

go
select COUNT ([DailyAttendance]) as [Total Attendance] from Attendance where [Day] 
                 between '2013-08-01' and '2013-08-31' and [DailyAttendance] = 1 and LaborID = 'La005'
go
select * from Attendance where LaborID = 'La11'
*/